package com.navi.heartbeat.job;

import com.alibaba.fastjson.JSON;
import com.navi.heartbeat.domain.IBisDataRepository;
import com.navi.heartbeat.domain.IRetHeartbeatRepository;
import com.navi.heartbeat.entity.DbTestInfo;
import com.navi.heartbeat.entity.ErrorCode;
import com.navi.heartbeat.entity.EtlConfVo;
import com.navi.heartbeat.entity.MBisDataD;
import com.navi.heartbeat.entity.SystemAlarm;
import com.navi.heartbeat.util.JdbcUtils;
import com.rabbitmq.client.Channel;
import com.rabbitmq.client.Connection;
import com.rabbitmq.client.ConnectionFactory;
import com.rabbitmq.client.AMQP.Queue.DeclareOk;

import com.rabbitmq.client.GetResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.time.LocalDateTime;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.amqp.core.ExchangeTypes;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.*;


/**
 * @author windson
 * @date 2017/8/9
 */

@Transactional(rollbackFor ={RuntimeException.class, Exception.class})
@Component
@Service("hbJob")
public class HeartBeatJob {

	private static final Logger logger = LoggerFactory.getLogger(HeartBeatJob.class);
	private static final int HTTP_OK = 200;
	private static final int EXPIRE_TIME = 720;
	private static final int ETL_TIME_OUT = 30;
	private static final int SEVERITY_SERIOUS = 1;
	private static final int SEVERITY_ORDINARY = 2;
	private static final int SEVERITY_NOT_SERIOUS = 1;
	@Autowired
	private JedisPool jedisPool;
	@Autowired
	private IRetHeartbeatRepository retHeartbeatRepository;
	@Autowired
	private IBisDataRepository bisDataRepository;
//	@Autowired
//	private ConnectionFactory connectionFactory;

	@Value("${spring.rabbitmq.exchange}")
	private String exchange;

	@Value("${spring.rabbitmq.queues}")
	private String queueNames;

	@Resource(name="payFactory")
	private ConnectionFactory payFactory;

	@Resource(name="connectionFactory")
	private ConnectionFactory connectionFactory;
	/**
	 * 检查Reids 系统
	 * @param jedis redis对象
	 * @param httpPro http参数
	 * @param alarmList 异常列表
	 * @param errorMsg 异常信息
	 */
	private void checkRedisSystem (Jedis jedis, MBisDataD httpPro, List<SystemAlarm> alarmList, String errorMsg) {
		if(!jedis.exists(httpPro.getExt1())){
			jedis.set(httpPro.getExt1(), "1");
			jedis.expire(httpPro.getExt1(), EXPIRE_TIME);

		}else{
			int cnt = Integer.parseInt(jedis.get(httpPro.getExt1())) + 1;
			int cntMax = 2;
			if(cnt > cntMax){
				SystemAlarm systemAlarm = new SystemAlarm();
				systemAlarm.setErrorMsg(errorMsg);
				systemAlarm.setSys(httpPro.getExt2());
				systemAlarm.setSeverity(SEVERITY_SERIOUS);
				systemAlarm.setErrorCode(ErrorCode.SERVICE_UNKNOWN_EXCEPTION.code);
				systemAlarm.setErrorDesc(ErrorCode.SERVICE_UNKNOWN_EXCEPTION.desc);
				systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));

				alarmList.add(systemAlarm);
				jedis.del(httpPro.getExt1());
			}else{
				jedis.set(httpPro.getExt1(),String.valueOf(cnt));
				jedis.expire(httpPro.getExt1(),EXPIRE_TIME);
			}
		}
	}

	/**
	 * 检测各系统是否正常运行
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm> checkSystem() {
		logger.info("Started checkSystem...");
		List<SystemAlarm> alarmList =new ArrayList<>();
		List<MBisDataD> bisDataDList = Optional.ofNullable(bisDataRepository.findAllByDataCate("HTPL"))
				.orElseGet(ArrayList::new);
		try(Jedis jedis = jedisPool.getResource()){
		bisDataDList.forEach(httpPro -> {
			Integer statusCode;
			try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
				HttpGet httpGet = new HttpGet(httpPro.getExt1());
				HttpResponse response = httpClient.execute(httpGet);
				statusCode = response.getStatusLine().getStatusCode();
				String errorMsg = EntityUtils.toString(response.getEntity(), "UTF-8");
				if(HTTP_OK != statusCode){
					checkRedisSystem(jedis, httpPro, alarmList, errorMsg);
				}else{
					if(jedis.exists(httpPro.getExt1())){
						jedis.del(httpPro.getExt1());
					}
				}
			} catch (Exception e) {
				SystemAlarm systemAlarm = new SystemAlarm();
				systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
				systemAlarm.setSys("checkSystem");
				systemAlarm.setErrorCode(ErrorCode.HEART_BEAT_HTTP_CLIENT_EXCEPTION.code);
				systemAlarm.setErrorDesc(ErrorCode.HEART_BEAT_HTTP_CLIENT_EXCEPTION.desc);
				systemAlarm.setErrorMsg(e.getMessage());
				systemAlarm.setSeverity(SEVERITY_NOT_SERIOUS);
				alarmList.add(systemAlarm);
				logger.error("checkSystem error :{}",e);
			}
		});
		}catch(Exception E){
			SystemAlarm systemAlarm = new SystemAlarm();
			systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
			systemAlarm.setSys("checkSystem");
			systemAlarm.setErrorCode(ErrorCode.REDIS_UNKNOWN_EXCEPTION.code);
			systemAlarm.setErrorDesc(ErrorCode.REDIS_UNKNOWN_EXCEPTION.desc);
			systemAlarm.setErrorMsg(E.getMessage());
			systemAlarm.setSeverity(SEVERITY_NOT_SERIOUS);
			alarmList.add(systemAlarm);
			logger.error("checkSystem error :{}",E);
		}
		return alarmList;
	}

	/**
	 * 监测定时表qrtz_etl_detail查看etl程序是否正常是否正常
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm> checkETL() {
		logger.info("Started checkETL...");
		List<SystemAlarm> alarmList = new ArrayList<>();
		try {
			String hql = "SELECT project_name as projectName,JOB_NAME as jobName,job_dsc as jobDesc," +
					"etl_timestamp as etlTimestamp,RUN_START_TIMESTAMP as runStartTime,RUN_END_TIMESTAMP as runEndTime," +
					"FINAL_END_TIMESTAMP as finalEndTime,shop as shopId,cell_type as cellType,opes as opeId," +
					" CASE WHEN FLOOR(TIME_TO_SEC (TIMEDIFF (now(), ETL_TIMESTAMP)) / 60 - TIME_OUT) > 0  and RUN_FLG !='N' and JOB_NAME !='LoadTest' "
					+
					" THEN '异常' ELSE '正常' END   AS STATE," +
					" FLOOR(TIME_TO_SEC ( TIMEDIFF (now(), ETL_TIMESTAMP) )/60) AS timeDeffi,case when job_name='LoadMeasureData' then ext_3 else time_out end as timeOut  " +
					" FROM etl_conf_d a WHERE VALID_FLG = 'Y' " +
					" and  JOB_NAME !='RELOAD'  ORDER BY STATE";

			QueryRunner mysqlRunner = JdbcUtils.getQmsMysqlRunner();
			List<EtlConfVo> list = mysqlRunner.query(hql, new BeanListHandler<>(EtlConfVo.class));
			String stateParam = "异常";
			list.forEach(etlConfVo -> {
				if (stateParam.equals(etlConfVo.getState())) {
					SystemAlarm systemAlarm = new SystemAlarm();

					systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
					systemAlarm.setSys("ETL");
					systemAlarm.setErrorCode(ErrorCode.ETL_TIMEOUT_EXCEPTION.code);
					systemAlarm.setErrorDesc(ErrorCode.ETL_TIMEOUT_EXCEPTION.desc);
					systemAlarm.setErrorMsg("ETL --> " + etlConfVo.getProjectName()
							+" --"+etlConfVo.getJobName()+"-- EtlTimestamp:"+etlConfVo.getEtlTimestamp()
							+" 执行超时\n RunStartTime --> ["
							+ etlConfVo.getRunStartTime() + "] RunEndTime --> [" + etlConfVo.getRunEndTime() + "] FinalEndTime --> ["
							+ etlConfVo.getFinalEndTime() + "]\n shopId --> [" + etlConfVo.getShopId() + "] cellType --> ["
							+ etlConfVo.getCellType() + "] opeId --> [" + etlConfVo.getOpeId() + "]\n"
							+ "执行超时 --> [" + etlConfVo.getTimeDeffi() + "min]");
					systemAlarm.setJobName(etlConfVo.getProjectName()+"["+etlConfVo.getJobDesc()+"]");
					systemAlarm.setEtlTimestamp(etlConfVo.getRunStartTime() + "--" + etlConfVo.getRunEndTime());
					systemAlarm.setTimeOut(etlConfVo.getTimeOut().toString());
					systemAlarm.setRealTimeOut(etlConfVo.getTimeDeffi().toString());
					systemAlarm.setShopId(etlConfVo.getShopId());

					if (etlConfVo.getTimeDeffi() > ETL_TIME_OUT) {
						systemAlarm.setSeverity(SEVERITY_SERIOUS);
					} else {
						systemAlarm.setSeverity(SEVERITY_ORDINARY);
					}

					alarmList.add(systemAlarm);
				}
			});
		} catch (Exception e) {
			SystemAlarm systemAlarm = new SystemAlarm();
			systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
			systemAlarm.setSys("checkETL");
			systemAlarm.setErrorCode(ErrorCode.HEART_BEAT_UNKNOWN_EXCEPTION.code);
			systemAlarm.setErrorDesc(ErrorCode.HEART_BEAT_UNKNOWN_EXCEPTION.desc);
			systemAlarm.setErrorMsg(e.getMessage());
			systemAlarm.setSeverity(SEVERITY_NOT_SERIOUS);
			alarmList.add(systemAlarm);
			logger.error("checkETL error :{}",e);
		}
		return alarmList;
	}

	/**
	 * 检查MQ是否堆积
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm> checkMQ() {
		logger.info("Started checkMQ...");
		List<SystemAlarm> alarmList = new ArrayList<>();
		try (Connection connection = connectionFactory.newConnection()) {
			Channel channel = connection.createChannel();
			channel.exchangeDeclare(exchange, ExchangeTypes.TOPIC, true);
			List<MBisDataD> mqDataDList  = bisDataRepository.findAllByDataCate("MQMI");
			mqDataDList.forEach((MBisDataD m) -> {
				int maxSize =Integer.parseInt(m.getExt2());
				DeclareOk ok = null;
				int count = 0;
				try {
					ok = channel.queueDeclare(m.getExt1(), true, false, false, null);
				} catch (IOException e) {
					SystemAlarm systemAlarm = new SystemAlarm();
					systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
					systemAlarm.setSeverity(SEVERITY_SERIOUS);
					systemAlarm.setErrorCode(ErrorCode.MQ_UNKNOWN_EXCEPTION.code);
					systemAlarm.setErrorDesc(m.getExt1() + "MQ声明异常");
					systemAlarm.setErrorMsg(m.getExt1() + " MQ声明异常" + e.getLocalizedMessage());
					systemAlarm.setSys("MQ_QUEUE");
					alarmList.add(systemAlarm);
				}
				if (!StringUtils.isEmpty(ok)) {
					count = ok.getMessageCount();
				}

				if(count > maxSize){
					SystemAlarm systemAlarm = new SystemAlarm();
					systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
					systemAlarm.setSeverity(SEVERITY_SERIOUS);
					systemAlarm.setErrorCode(ErrorCode.MQ_OVERSTOCK_EXCEPTION.code);
					systemAlarm.setErrorDesc(ErrorCode.MQ_OVERSTOCK_EXCEPTION.desc);
					systemAlarm.setErrorMsg(m.getExt1() + " OVER SIZE :预警值: [" + maxSize + "],实际值: [" + count + "]");
					systemAlarm.setSys("MQ_QUEUE");
					alarmList.add(systemAlarm);
				}
			});
			channel.close();
		} catch (Exception e) {
			SystemAlarm systemAlarm = new SystemAlarm();
			systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
			systemAlarm.setErrorDesc(ErrorCode.MQ_CONNECTION_EXCEPTION.desc);
			systemAlarm.setErrorCode(ErrorCode.MQ_CONNECTION_EXCEPTION.code);
			systemAlarm.setErrorMsg("获取Rabbit MQ 连接异常: " + e.toString());
			systemAlarm.setSys("Rabbit_MQ");
			alarmList.add(systemAlarm);
			logger.error("Rabbit_MQ error :{}",e);
		}

		try (Connection connection = payFactory.newConnection()) {
			Channel channel = connection.createChannel();
			channel.exchangeDeclare(exchange, ExchangeTypes.TOPIC, true);
			List<MBisDataD> mqDataDList  = bisDataRepository.findAllByDataCate("MQMI");
			mqDataDList.forEach((MBisDataD m) -> {
				int maxSize =Integer.parseInt(m.getExt2());
				DeclareOk ok = null;
				int count = 0;
				try {
					ok = channel.queueDeclare(m.getExt1(), true, false, false, null);
				} catch (IOException e) {
					SystemAlarm systemAlarm = new SystemAlarm();
					systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
					systemAlarm.setSeverity(SEVERITY_SERIOUS);
					systemAlarm.setErrorCode(ErrorCode.MQ_UNKNOWN_EXCEPTION.code);
					systemAlarm.setErrorDesc(m.getExt1() + "MQ声明异常");
					systemAlarm.setErrorMsg(m.getExt1() + " MQ声明异常" + e.getLocalizedMessage());
					systemAlarm.setSys("MQ_QUEUE");
					alarmList.add(systemAlarm);
				}
				if (!StringUtils.isEmpty(ok)) {
					count = ok.getMessageCount();
				}

				if(count > maxSize){
					SystemAlarm systemAlarm = new SystemAlarm();
					systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
					systemAlarm.setSeverity(SEVERITY_SERIOUS);
					systemAlarm.setErrorCode(ErrorCode.MQ_OVERSTOCK_EXCEPTION.code);
					systemAlarm.setErrorDesc(ErrorCode.MQ_OVERSTOCK_EXCEPTION.desc);
					systemAlarm.setErrorMsg(m.getExt1() + " OVER SIZE :预警值: [" + maxSize + "],实际值: [" + count + "]");
					systemAlarm.setSys("MQ_QUEUE");
					alarmList.add(systemAlarm);
				}
			});
			channel.close();
		} catch (Exception e) {
			SystemAlarm systemAlarm = new SystemAlarm();
			systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
			systemAlarm.setErrorDesc(ErrorCode.MQ_CONNECTION_EXCEPTION.desc);
			systemAlarm.setErrorCode(ErrorCode.MQ_CONNECTION_EXCEPTION.code);
			systemAlarm.setErrorMsg("获取Rabbit MQ 连接异常: " + e.toString());
			systemAlarm.setSys("Rabbit_MQ");
			alarmList.add(systemAlarm);
			logger.error("Rabbit_MQ error :{}",e);
		}
		return alarmList;
	}

	/**
	 * 检查DB(包括Redis) 连接是否正常
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm> checkDB() {
		logger.info("Started checkDB...");
		List<SystemAlarm> alarmList = new ArrayList<>();
		alarmList.addAll(testDBQuery());
		alarmList.addAll(testJedis());
		return alarmList;
	}

	/**
	 * 检查 MySQL, GP, Oracle查询是否正常
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm> testDBQuery() {
		List<SystemAlarm> alarmList = new ArrayList<>(16);
		List<DbTestInfo> dbTestInfoList = new ArrayList<>(4);

//		QueryRunner dpsMysqlRunner = JdbcUtils.getDpsMysqlRunner();
//		QueryRunner mpsMydqlRunner = JdbcUtils.getMpsMysqlRunner();
		QueryRunner qmsGpRunner = JdbcUtils.getQmsGpRunner();
		QueryRunner biOracleRunner = JdbcUtils.getBiOracleRunner();

//		String dpsMysql =" select USER_ID from scm_dps_bisuser limit 1 ";
//		String mpsMysql =" select USER_ID from scm_mps_bisuser limit 1 ";
		String qmsGp =" select panel_id  from wpp_sht_info limit 1 ";
		String biOracle =" SELECT ITEM FROM EDWUSER.ITEMCONFIG where ROWNUM = 1  ";

//		dbTestInfoList.add(new DbTestInfo(dpsMysql, dpsMysqlRunner, "DPS-MYSQL"));
//		dbTestInfoList.add(new DbTestInfo(dpsMysql, dpsMysqlRunner, "DPS-MYSQL"));
//		dbTestInfoList.add(new DbTestInfo(mpsMysql, mpsMydqlRunner, "MPS-MYSQL"));
		dbTestInfoList.add(new DbTestInfo(biOracle, biOracleRunner, "BI-ORACLE"));
		dbTestInfoList.add(new DbTestInfo(qmsGp, qmsGpRunner, "QMS-GP"));
		alarmList.addAll(testDB(dbTestInfoList));
		return alarmList;
	}

	/**
	 * 测试DB查询公共方法
	 * @param dbTestInfoList 测试DB基本信息
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm> testDB(List<DbTestInfo> dbTestInfoList) {
		List<SystemAlarm> alarmList = new ArrayList<>(dbTestInfoList.size());
		dbTestInfoList.forEach(dbTestInfo -> {
			String sql = dbTestInfo.getSql();
			String sysName = dbTestInfo.getSysName();
			QueryRunner queryRunner = dbTestInfo.getQueryRunner();

			try {
				queryRunner.query(sql,new ColumnListHandler<String>());
			} catch (SQLException e) {
				SystemAlarm systemAlarm = new SystemAlarm();
				systemAlarm.setSys(sysName);
				systemAlarm.setErrorMsg(sysName + "数据库查询失败！Message: " + e.getLocalizedMessage());
				systemAlarm.setErrorCode(ErrorCode.DB_SQL_EXECUTE_EXCEPTION.code);
				systemAlarm.setErrorDesc(ErrorCode.DB_SQL_EXECUTE_EXCEPTION.desc);
				systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
				systemAlarm.setSeverity(SEVERITY_SERIOUS);
				alarmList.add(systemAlarm);
				logger.error("{} error :{}",sysName,e);
			}
		});

		return alarmList;
	}

	/**
	 * 测试Reis是否正常
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm>  testJedis() {
		List<SystemAlarm> alarmList = new ArrayList<>(16);
		String redisSys ="redisSysMonitor";
		try (Jedis redis = jedisPool.getResource()){
			if(!redis.exists(redisSys)){
				redis.sadd(redisSys,redisSys);
				redis.persist(redisSys);
			}
		}catch (Exception e){
			SystemAlarm systemAlarm = new SystemAlarm();
			systemAlarm.setSys("Redis");
			systemAlarm.setSeverity(SEVERITY_SERIOUS);
			systemAlarm.setErrorCode(ErrorCode.REDIS_JEDIS_POOL_EXCEPTION.code);
			systemAlarm.setErrorDesc(ErrorCode.REDIS_JEDIS_POOL_EXCEPTION.desc);
			systemAlarm.setErrorMsg("Jedis 异常" + e.getLocalizedMessage());
			alarmList.add(systemAlarm);
			logger.error("Jedis 异常:{}",e);
		}
		return alarmList;
	}
	private List<SystemAlarm> loadSysAlarm() {
		logger.info("Started loadSysAlarm 每次 10000笔...");
		List<SystemAlarm> alarmList = new ArrayList<>(16);
		try (Connection connection = connectionFactory.newConnection()) {
			final Channel channel = connection.createChannel();
			channel.exchangeDeclare("qmsExchange", ExchangeTypes.TOPIC, true);
			List<GetResponse> responseList = new ArrayList<>(16);
			for(int i=0;i<10000;i++){
				final GetResponse response = channel.basicGet("SYS_ALARM", false);
				if(response == null) {
					continue;
				}
				if (response.getBody() == null) {
					continue;
				}
				String message = new String(response.getBody(), "utf-8");
				SystemAlarm alarm = JSON.parseObject(message, SystemAlarm.class);
				if("RTM".equals(alarm.getSys())){
					logger.info("alarm.getErrorMsg() :{}",alarm.getErrorMsg());
				}
				alarmList.add(alarm);
				responseList.add(response);


			}
			if(responseList.size()<=0){
				logger.info("SYS_ALARM QUEUE 为空！");
				return alarmList;
			}
			//消费Q
			responseList.forEach(r -> {
				try {
					channel.basicAck(r.getEnvelope().getDeliveryTag(), false);
				} catch (IOException e) {
					logger.error(" ACK ERROR MESSAGE :",e);
					e.printStackTrace();
				}
			});
		} catch (Exception ex) {
			logger.error("Rabbit MQ 连接异常, {}", ex);
		}
		return alarmList;
	}



	/**
	 * 检查 glass 表中站点evt_timestamp最后一笔时间距离当前时间大于1小时站点
	 * @return List<SystemAlarm>
	 */
	private List<SystemAlarm> testOpeId_EvtTime() {
		logger.info("检查 glass 表中站点evt_timestamp最后一笔时间距离当前时间大于1小时站点");
		List<SystemAlarm> alarmList = new ArrayList<>(16);
		QueryRunner qmsGpRunner = JdbcUtils.getQmsGpRunner();
		String sysName="loadFileMonitoring";
		String sql =" select T.ope_id||'最后一次更新时间:'||T.max_evt_timestamp as ope_evttime from (\n" +
				" select shop_id,ope_id,max(evt_timestamp) as max_evt_timestamp ,\n" +
				" case when now()-max(evt_timestamp) >interval '1 hour' then 'N' else 'Y' end as flag\n" +
				" from wpp_cdefect_glass_f \n" +
				" where ope_id in('L1110','L2110','7200','L1240','4600','4610','5600','L4210','L2140','L4110')  \n" +
				" and evt_timestamp>=now()-interval '25 hour'  \n" +
				" and evt_timestamp<= now()\n" +
				" and real_flg='Y'\n" +
				" group by shop_id,ope_id   \n" +
				" union all\n" +
				" select shop_id,ope_id,max(evt_timestamp) as max_evt_timestamp ,\n" +
				" case when now()-max(evt_timestamp) >interval '1 hour' then 'N' else 'Y' end as flag\n" +
				" from wpp_adefect_glass_f \n" +
				" where ope_id in('A7600','A1353','A535J','A235J','A4350','A5350','A185J','A435J','A385J','A2350',\n" +
				"'A1850','A4311','A1350','A3850','A7602','A3353','A7750','A3890','A3311','A3853')  \n" +
				" and evt_timestamp>=now()-interval '25 hour'  \n" +
				" and evt_timestamp<= now()\n" +
				" and real_flg='Y'\n" +
				" group by shop_id,ope_id   \n" +
				" union all\n" +
				" select shop_id,ope_id,max(evt_timestamp) as max_evt_timestamp ,\n" +
				" case when now()-max(evt_timestamp) >interval '1 hour' then 'N' else 'Y' end as flag\n" +
				" from wpp_fdefect_glass_f \n" +
				" where ope_id in('C1341','C1351','C2341','C4341','C7341','C3341','C2351','C1353','C7353','C4550','C7560',\n" +
				"'C3351','C4560','C759I','C2311','C7311','C4351','C7550','C7590')  \n" +
				" and evt_timestamp>=now()-interval '25 hour'  \n" +
				" and evt_timestamp<= now()\n" +
				" and real_flg='Y'\n" +
				" group by shop_id,ope_id\n" +
				" )T  where T.flag ='N'\n" +
				" order by T.shop_id,T.ope_id,T.max_evt_timestamp  ";
		try{
			List<String> resultList=qmsGpRunner.query(sql,new ColumnListHandler<String>());
			if(resultList==null||resultList.size()<=0){
				return alarmList;
			}
			for(String str:resultList){
				SystemAlarm systemAlarm = new SystemAlarm();
				systemAlarm.setSys(sysName);
				systemAlarm.setErrorMsg(str);
				systemAlarm.setErrorCode(ErrorCode.HEART_BEAT_GP_GLASS_OPE_EVTTIME.code);
				systemAlarm.setErrorDesc(ErrorCode.HEART_BEAT_GP_GLASS_OPE_EVTTIME.desc);
				systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
				systemAlarm.setSeverity(SEVERITY_SERIOUS);
				alarmList.add(systemAlarm);
			}
		}catch (Exception e){
			SystemAlarm systemAlarm = new SystemAlarm();
			systemAlarm.setSys(sysName);
			systemAlarm.setErrorMsg(sysName + "数据库查询失败！Message: " + e.getLocalizedMessage());
			systemAlarm.setErrorCode(ErrorCode.HEART_BEAT_GP_GLASS_OPE_EVTTIME.code);
			systemAlarm.setErrorDesc(ErrorCode.HEART_BEAT_GP_GLASS_OPE_EVTTIME.desc);
			systemAlarm.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
			systemAlarm.setSeverity(SEVERITY_SERIOUS);
			alarmList.add(systemAlarm);
			logger.error("{} error :{}",sysName,e);
		}

		return alarmList;
	}
	@Transactional(rollbackFor ={RuntimeException.class, Exception.class})
	public void checkHeartBeat() {
		logger.info("CheckHeartBeat Job Start:[{}]", LocalDateTime.now());
		List<SystemAlarm> alarms =new ArrayList<>();
		alarms.addAll(checkSystem());
		alarms.addAll(checkETL());
		alarms.addAll(checkDB());
		alarms.addAll(checkMQ());
		alarms.addAll(loadSysAlarm());
		alarms.addAll(testOpeId_EvtTime());
		alarms.forEach(e -> retHeartbeatRepository.save(e));
		logger.info("CheckHeartBeat Job End:[{}]", LocalDateTime.now());
	}
}
